#region Using Statements

using System;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Web.SessionState;
using Dotss.Core;

#endregion

namespace Dotss.Client
{

	/// <summary>
	/// A server locator that will use itself in local memory and write to an external SqlServer database
	/// </summary>
	public class MsSqlDbServerLocator : ServerRO, ServerLocator, DataAccess 
	{

        #region Fields

        internal readonly static Version CURRENT_VERSION = new Version(0, 1, 0, 0);
        private static string _ConnectionString;
	    private static bool _SchemaVerificationRequired = true;

		#endregion
	    
        public MsSqlDbServerLocator()
		{

            if (!SchemaVerificationRequired) return;

			// Verify that the correct version of the schema is available
			VerifyCurrentSchema();

		}

		#region Implementation of ServerLocator

		public ServerRO GetServer(ClientConfig config)
		{
			return this;
		}

		#endregion

		#region Implementation of DataAccess

		void DataAccess.Remove(string sessionId, string appId, string envId)
		{

			SqlConnection conn = GetConnection();

			using (conn)
			{

				SqlCommand cmd = conn.CreateCommand();
				cmd.CommandType = CommandType.StoredProcedure;
				cmd.CommandText = "DOTSS.RemoveItem";
				cmd.Parameters.AddWithValue("@SessionID", sessionId);
				cmd.Parameters.AddWithValue("@ApplicationName", appId);
				cmd.Parameters.AddWithValue("@EnvName", envId);
				cmd.Parameters.AddWithValue("@LockID", 0);
				cmd.ExecuteNonQuery();

			}

		}

		void DataAccess.ResetTimeout(SessionArgs session)
		{
			SqlConnection conn = GetConnection();

			using (conn)
			{

				SqlCommand cmd = conn.CreateCommand();
				cmd.CommandType = CommandType.StoredProcedure;
				cmd.CommandText = "DOTSS.ResetTimeout";
				cmd.Parameters.AddWithValue("@SessionID", session.SessionID);
				cmd.Parameters.AddWithValue("@ApplicationName", session.ApplicationName);
				cmd.Parameters.AddWithValue("@EnvName", session.EnvironmentName);
				cmd.Parameters.AddWithValue("@TimeoutMinutes", session.TimeoutMins);
				cmd.ExecuteNonQuery();

			}

		}

		void DataAccess.CreateNew(SessionArgs session)
		{

			using (SqlConnection conn = GetConnection())
			{
				SqlCommand cmd = conn.CreateCommand();
				cmd.CommandType = CommandType.StoredProcedure;
				cmd.CommandText = "DOTSS.CreateNewItem";
				cmd.Parameters.AddWithValue("@SessionID", session.SessionID);
				cmd.Parameters.AddWithValue("@TimeoutMinutes", session.TimeoutMins);
				cmd.Parameters.AddWithValue("@AppName", session.ApplicationName);
				cmd.Parameters.AddWithValue("@EnvName", session.EnvironmentName);
				cmd.ExecuteNonQuery();
			}

		}

		void DataAccess.Initialize(SessionArgs session)
		{
			throw new System.NotImplementedException();
		}

		void DataAccess.ReleaseLock(SessionArgs session)
		{

			SqlConnection conn = GetConnection();

			using (conn)
			{

				SqlCommand cmd = conn.CreateCommand();
				cmd.CommandType = CommandType.StoredProcedure;
				cmd.CommandText = "DOTSS.ReleaseLock";
				cmd.Parameters.AddWithValue("@SessionID", session.SessionID);
				cmd.Parameters.AddWithValue("@ApplicationName", session.ApplicationName);
				cmd.Parameters.AddWithValue("@EnvName", session.EnvironmentName);
				cmd.Parameters.AddWithValue("@LockID", session.LockID);
				cmd.Parameters.AddWithValue("@TimeoutMinutes", session.TimeoutMins);
				cmd.ExecuteNonQuery();

			}

		}

		void DataAccess.SetItem(SessionArgs session, byte[] items)
		{

			using (SqlConnection conn = GetConnection())
			{
				SqlCommand cmd = conn.CreateCommand();
				cmd.CommandType = CommandType.StoredProcedure;
				cmd.CommandText = "DOTSS.SetItem";
				cmd.Parameters.AddWithValue("@SessionID", session.SessionID);
				cmd.Parameters.AddWithValue("@ApplicationName", session.ApplicationName);
				cmd.Parameters.AddWithValue("@EnvName", session.EnvironmentName);
				cmd.Parameters.AddWithValue("@TimeoutMinutes", session.TimeoutMins);
				cmd.Parameters.AddWithValue("@LockID", session.LockID);
				cmd.Parameters.AddWithValue("@Items", items);
				cmd.ExecuteNonQuery();
			}

		}

		protected override DataAccess Data
		{
			get
			{
				// NOTE: Not used in this implementation
				return this;
			}
		}

		byte[] DataAccess.GetItem(SessionArgs args, bool isExclusive, out bool locked, out TimeSpan lockAge, out int lockId, out SessionStateActions actions)
		{

			SqlConnection conn = GetConnection();

			byte[] item = new byte[] {};
			locked = false;
			lockAge = TimeSpan.Zero;
			lockId = 0;

			using (conn)
			{

				SqlCommand cmd		= conn.CreateCommand();
				cmd.CommandType		= CommandType.StoredProcedure;
				cmd.CommandText		= "DOTSS.GetItem";
				cmd.Parameters.AddWithValue("@SessionID", args.SessionID);
				cmd.Parameters.AddWithValue("@ApplicationName", args.ApplicationName);
				cmd.Parameters.AddWithValue("@EnvName", args.EnvironmentName);
				cmd.Parameters.AddWithValue("@Lock", isExclusive ? 1 : 0);

				SqlParameter parm		= cmd.CreateParameter();
				parm.ParameterName		= "@Locked";
				parm.SqlDbType			= SqlDbType.TinyInt;
				parm.Direction			= ParameterDirection.Output;
				cmd.Parameters.Add(parm);

				SqlDataReader rdr		= cmd.ExecuteReader(CommandBehavior.SingleRow);
				if (rdr.Read())
				{

					item = (byte[]) rdr["Items"];
					lockId = (short)rdr["LockID"];
					lockAge = DateTime.Now.Subtract(rdr.GetDateTime(rdr.GetOrdinal("LockDate")));

				}

			}

			actions = SessionStateActions.None;

			return item;

		}

		#endregion

		#region Private Methods

		/// <summary>
		/// Get the specified SqlConnection based on settings from the config file
		/// </summary>
		/// <returns></returns>
		internal static SqlConnection GetConnection()
		{

			SqlConnection conn = new SqlConnection(ConnectionString);
			conn.Open();

			return conn;

		}

		private void VerifyCurrentSchema()
		{

			try
			{
				using (SqlConnection conn = GetConnection())
				{
				    // Use straight text queries here to ensure the objects are available
				    Version version = GetVersionFromDb(conn);

				    if (version != CURRENT_VERSION)		// Upgrade required
					{
						PatchFromVersion(version, conn);
					}
				}
			} catch (SqlException)
			{
			    Trace.WriteLine("Unable to access the DOTSS.SchemaVersion table, installing fresh");
                using (SqlConnection conn = GetConnection())
                {
                    PatchFromVersion(null, conn);
                }
			}
            finally
			{
				// Do nothing.... yet
			}

		}

	    internal static Version GetVersionFromDb(SqlConnection conn)
	    {
	        SqlCommand cmd      = conn.CreateCommand();
	        cmd.CommandText     = "SELECT TOP 1 Version FROM DOTSS.SchemaVersion ORDER BY CreateDate DESC";
	        cmd.CommandType     = CommandType.Text;
	        return new Version(cmd.ExecuteScalar().ToString());
	    }

	    internal Version PatchFromVersion(Version version, SqlConnection conn)
		{

            // Verify that we are not current before attempting to proceed
            if (version != null && version == CURRENT_VERSION) return version;

		    SqlCommand cmd = conn.CreateCommand();
		    cmd.CommandType = CommandType.Text;

            // Pivot here for a FRESH install
            if (version == null)
            {
                cmd.CommandText = MsSqlScripts.v0_1_Install;
                cmd.ExecuteNonQuery();
            }

            // Patch other versions here

            // Get the new version and repeat
	        Version newVersion = GetVersionFromDb(conn);
            Version outVersion = PatchFromVersion(newVersion, conn);

	        return outVersion;

        }

		#endregion

        #region Internal Properties

        /// <summary>
        /// An internal connectionstring property to allow injection from a Unit Test project of a test connection
        /// </summary>
	    internal static string ConnectionString
	    {
            get
            {
                if (_ConnectionString == null) ConnectionString = ClientConfig.Settings.ConnectionString;
                return _ConnectionString;
            }
            set
            {
                _ConnectionString = value;
            }
	    }

        /// <summary>
        /// Do we need to verify the schema at startup
        /// </summary>
	    internal static bool SchemaVerificationRequired
	    {
            get { return _SchemaVerificationRequired;  }
            set { _SchemaVerificationRequired = value;  }
	    }

        #endregion

		#region IDisposable Members

		public void Dispose()
		{
			// Do nothing
		}

		#endregion

	}

}
